package com.zm.demo.mysql.connect;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Random;

public class AutoIncrementDemo {

    static String url = "jdbc:mysql://localhost:3306/local1?user=root&password=root&characterEncoding=utf8&useSSL=false";

    public static void main(String[] args) throws Exception {
        //范围：connection
        Long start = System.currentTimeMillis();
        getGeneratedKeys();
        System.out.println("getGeneratedKeys 耗时： " + (System.currentTimeMillis() - start) + " ms \n");

        //范围：Statement
        start = System.currentTimeMillis();
        selectLastId();
        System.out.println("selectLastId 耗时： " + (System.currentTimeMillis() - start) + " ms \n");

        start = System.currentTimeMillis();
        updatableResultSets();
        System.out.println("updatableResultSets 耗时： " + (System.currentTimeMillis() - start) + " ms \n");
    }


    private static void getGeneratedKeys() throws Exception{
        Connection connection = null;
        Statement statement = null;
        int updateCount = 0;
        try {
            connection = DriverManager.getConnection(url);
            statement = connection.createStatement();
            String sql = "INSERT INTO `user`(`name`, `age`, `email`) VALUES ('zm1111111111', 188, 'adadasfafeadsf@dasfads.com');";
            statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
            ResultSet rs = statement.getGeneratedKeys();
            int autoIncKey = -1;
            if (rs.next()){
                autoIncKey = rs.getInt(1);
            }
            System.out.println("getGeneratedKeys key: " + autoIncKey);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            close(connection,statement,null);
        }
    }


    private static void selectLastId() throws Exception{
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DriverManager.getConnection(url);
            statement = connection.createStatement();
            String sql = "INSERT INTO `user`(`name`, `age`, `email`) VALUES ('zm1111111111', 188, 'adadasfafeadsf@dasfads.com');";
            statement.executeUpdate(sql);
            ResultSet rs = statement.executeQuery("SELECT LAST_INSERT_ID()");
            int autoIncKey = -1;
            if (rs.next()){
                autoIncKey = rs.getInt(1);
            }
            System.out.println("LAST_INSERT_ID key: " + autoIncKey);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            close(connection,statement,null);
        }
    }

    private static void updatableResultSets() throws Exception{
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DriverManager.getConnection(url);
            statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = statement.executeQuery("SELECT id, name, age, email from user  limit 1");
            rs.moveToInsertRow();
            rs.updateString("name", "updatableResultSets");
            rs.insertRow();
            rs.last();

            int autoIncKey = rs.getInt("id");
            System.out.println("updatableResultSets key: " + autoIncKey);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            close(connection,statement,null);
        }
    }

    private static void close(Connection connection, Statement statement, ResultSet rs) throws Exception{
        if (rs != null){
            rs.close();
        }
        if (statement != null){
            statement.close();
        }
        if (connection != null){
            connection.close();
        }
    }
}
